package com.yh.service.impl;

import com.yh.domain.User;
import com.yh.service.IExcelService;
import lombok.extern.log4j.Log4j2;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 导入导出excel poi
 *
 * @author yh
 * @date 2021-4-4
 */
@Service
@Log4j2
public class ExcelServiceImpl implements IExcelService {

    @Override
    public HSSFWorkbook exportExcel() {
        //构建数据
        List<User> users = new ArrayList<>();
        users.add(User.builder().id("1").nickName("张三").phone("18222222222").age(20).sex("男").build());
        users.add(User.builder().id("2").nickName("李四").phone("18222222222").age(20).sex("男").build());
        users.add(User.builder().id("3").nickName("王五").phone("18222222222").age(20).sex("男").build());
        users.add(User.builder().id("4").nickName("陈六").phone("18222222222").age(20).sex("男").build());
        users.add(User.builder().id("5").nickName("余七").phone("18222222222").age(20).sex("男").build());
        users.add(User.builder().id("6").nickName("杨八").phone("18222222222").age(20).sex("男").build());
        //形成excel
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("用户信息");
        HSSFRow headerRow = sheet.createRow(0);
        //样式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFCell cell0 = headerRow.createCell(0);
        cell0.setCellValue("编号");
        HSSFCell cell1 = headerRow.createCell(1);
        cell1.setCellValue("姓名");
        HSSFCell cell2 = headerRow.createCell(2);
        cell2.setCellValue("电话");
        HSSFCell cell3 = headerRow.createCell(3);
        cell3.setCellValue("年级");
        HSSFCell cell4 = headerRow.createCell(4);
        cell4.setCellValue("性别");

        for(int i=0;i<users.size();i++){
            headerRow = sheet.createRow((int) i + 1);
            HSSFCell cell = headerRow.createCell(0);
            cell.setCellValue(users.get(i).getId());

            cell = headerRow.createCell(1);
            cell.setCellValue(users.get(i).getNickName());

            cell = headerRow.createCell(2);
            cell.setCellValue(users.get(i).getPhone());

            cell = headerRow.createCell(3);
            cell.setCellValue(users.get(i).getAge());

            cell = headerRow.createCell(4);
            cell.setCellValue(users.get(i).getSex());
        }
        return workbook;
    }

    @Override
    public void importExcel(MultipartFile file) {
        InputStream inputStream = null;
        HSSFWorkbook workbook = null;
        try {
            inputStream = file.getInputStream();
            workbook = new HSSFWorkbook(inputStream);
        } catch (IOException e) {
            log.info(e);
        }
        User user = null;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet hssfSheet = workbook.getSheetAt(i);
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                user = User.builder().build();
                HSSFCell cell = hssfRow.getCell(1);
                if (cell != null) {
                    user.setNickName(cell.getStringCellValue());
                }
                HSSFCell cell1 = hssfRow.getCell(2);
                if (cell1 != null) {
                    user.setPhone(cell1.getStringCellValue());
                }
                HSSFCell cell2 = hssfRow.getCell(3);
                if (cell2 != null) {
                    user.setAge(Double.valueOf(cell2.getNumericCellValue()).intValue());
                }
                HSSFCell cell3 = hssfRow.getCell(4);
                if (cell3 != null) {
                    user.setSex(cell3.getStringCellValue());
                }
                System.out.println(user);
            }
        }
    }
}
